package com.borya.dao.impl;

import com.borya.dao.SdkAppJobNumberDao;
import com.borya.framework.annotation.Component;
import com.borya.framework.core.ServerSqlSession;
import com.borya.model.db.JobNumberAudit;
import com.borya.model.db.Merchant;
import com.borya.model.db.User;
import com.borya.model.entity.*;
import com.borya.util.AliOSSUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

import java.util.ArrayList;
import java.util.List;

@Component("sdkAppJobNumberDaoImpl")
public class SdkAppJobNumberDaoImpl extends BaseDAOImpl implements SdkAppJobNumberDao {

    public Logger log = Logger.getLogger(getClass());

    @Override
    public List<UserDataModel> getJobNumberUserList(String merchantId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select u.create_time,a.userid,a.user_name,a.cityname,a.createtime,a.is_main,a.phone,b.left_fee,b.deduction_fee");
            sb.append(" from tb_user_client a ");
            sb.append(" left join tb_user_account b on a.userid=b.user_id ");
            sb.append(" left join tb_job_number_audit u on u.user_id=a.userid ");
            sb.append(" where a.merchant_id=? and a.flag != -1 ");
            sb.append(" order by a.createtime asc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<UserDataModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        UserDataModel u = new UserDataModel();
                        u.setUserId(rs.getString("a.userid"));
                        u.setUserName(rs.getString("a.user_name"));
                        u.setCityName(rs.getString("a.cityname"));
                        if (StringUtils.isBlank(rs.getString("u.create_time"))) {
                            u.setCreateTime(rs.getString("a.createtime"));
                        } else {
                            u.setCreateTime(rs.getString("u.create_time"));
                        }
                        u.setIsMain(rs.getString("a.is_main"));
                        u.setPhone(rs.getString("a.phone"));
                        u.setDeductionFee(rs.getString("b.deduction_fee"));
                        u.setLeftFee(rs.getString("b.left_fee"));
                        list.add(u);
                    }
                }
                return list;
            }, merchantId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public List<JobNumberAuditModel> getJobNumberAuditList(String merchantId, int type) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select son_user_phone,son_user_name,create_time,city_name,order_status ");
            sb.append(" from tb_job_number_audit ");
            sb.append(" where order_status IN (3,4) "); // 在途
            if (1 == type) {
                sb.append(" and merchant_id = ? ");
            } else {
                sb.append(" and son_user_phone = ? ");
            }
            sb.append(" order by create_time desc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<JobNumberAuditModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        JobNumberAuditModel audit = new JobNumberAuditModel();
                        audit.setPhone(rs.getString("son_user_phone"));
                        audit.setUserName(rs.getString("son_user_name"));
                        audit.setCreateTime(rs.getString("create_time"));
                        audit.setCityName(rs.getString("city_name"));
                        audit.setOrderStatus("正在审核中");
                        list.add(audit);
                    }
                }
                return list;
            }, merchantId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public JobNumberCountInfo getJobNumberCountInfo(String appId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select personal_job_number_count,company_job_number_count ");
            sb.append(" from tb_sdk_app_conf ");
            sb.append(" where app_id = ? ");

            return session.executeQuery(sb.toString(), rs -> {
                if (rs != null) {
                    while (rs.next()) {
                        JobNumberCountInfo info = new JobNumberCountInfo();
                        info.setPersonalJobNumberCount(rs.getString("personal_job_number_count"));
                        info.setCompanyJobNumberCount(rs.getString("company_job_number_count"));
                        return info;
                    }
                }
              return null;
            }, appId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public List<ManageLogModel> getManageLogList(String dealerId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select audit_type,auto_audit_result,create_time,son_user_name,city_name,son_user_phone,order_status,auto_audit_desc,remarks ");
            sb.append(" from tb_job_number_audit ");
            sb.append(" where dealer_id=? order by create_time desc ");

            List<ManageLogModel> logList = new ArrayList<>();
            session.executeQuery(sb.toString(), rs -> {
                if (rs != null) {
                    while (rs.next()) {
                        ManageLogModel model = new ManageLogModel();
                        model.setPhone(rs.getString("son_user_phone"));
                        model.setUserName(rs.getString("son_user_name"));
                        model.setCreateTime(rs.getString("create_time"));
                        model.setCityName(rs.getString("city_name"));
                        model.setOrderStatus(rs.getString("order_status"));
                        if (!rs.getString("order_status").equals("3") && !rs.getString("order_status").equals("4")) {
                            if (1 == rs.getInt("audit_type")) {
                                model.setMsgCont(rs.getString("auto_audit_desc"));
                            } else {
                                model.setMsgCont(rs.getString("remarks"));
                            }
                        }
                        logList.add(model);
                    }
                }
                return null;
            }, dealerId);

            sb.delete(0, sb.length());
            sb.append("select createtime,user_name,cityname,phone ");
            sb.append(" from tb_user_removed ");
            sb.append(" where dealer_id=? order by createtime desc ");
            session.executeQuery(sb.toString(), rs -> {
                if (rs != null) {
                    while (rs.next()) {
                        ManageLogModel model = new ManageLogModel();
                        model.setPhone(rs.getString("phone"));
                        model.setUserName(rs.getString("user_name"));
                        model.setCreateTime(rs.getString("createtime"));
                        model.setCityName(rs.getString("cityname"));
                        model.setOrderStatus("5"); // 已删除
                        logList.add(model);
                    }
                }
                return null;
            }, dealerId);

            return logList;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public User getJobnumberUserByPhone(String phone, int detailType) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT dealer_id,userid,citycode,cityname,invite_code");
            sb.append(",user_name,phone,flag,source_type");
            sb.append(" FROM tb_user_client");
            sb.append(" WHERE phone=? and detail_type = ? ");
            sb.append(" and flag=1 ");
            sb.append(" limit 1");

            return session.executeQuery(sb.toString(), rs -> {
                if (rs != null && rs.next()) {
                    User user = new User();
                    user.setDealerId(rs.getString("dealer_id"));
                    user.setUserId(rs.getString("userid"));
                    user.setCityCode(rs.getString("citycode"));
                    user.setCityName(rs.getString("cityname"));
                    user.setInviteCode(rs.getString("invite_code"));
                    user.setUserName(rs.getString("user_name"));
                    user.setDealerId(rs.getString("dealer_id"));
                    user.setPhone(rs.getString("phone"));
                    user.setFlag(rs.getInt("flag"));
                    user.setSourceType(rs.getInt("source_type"));
                    return user;
                }

                return new User();
            }, phone,detailType);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public List<JobNumberAuditModel> checkZaiTu(String phone,int detailType) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select son_user_phone,son_user_name,create_time,city_name,order_status ");
            sb.append(" from tb_job_number_audit ");
            sb.append(" where order_status IN (3,4) "); // 在途
            sb.append(" and son_user_phone = ? and detail_type = ? ");
            sb.append(" order by create_time desc ");
            return session.executeQuery(sb.toString(), rs -> {
                List<JobNumberAuditModel> list = new ArrayList<>();
                if (rs != null) {
                    while (rs.next()) {
                        JobNumberAuditModel audit = new JobNumberAuditModel();
                        audit.setPhone(rs.getString("son_user_phone"));
                        audit.setUserName(rs.getString("son_user_name"));
                        audit.setCreateTime(rs.getString("create_time"));
                        audit.setCityName(rs.getString("city_name"));
                        audit.setOrderStatus("正在审核中");
                        list.add(audit);
                    }
                }
                return list;
            }, phone,detailType);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }

    @Override
    public User getUserByIdCardIsMain(String idCard, int detailType, int isMain) {
        StringBuilder sql = new StringBuilder(256);
        sql.append("SELECT b.userid");
        sql.append(" FROM tb_user_client_detail b");
        sql.append(" left join tb_user_client a ");
        sql.append(" on a.userid=b.userid");
        sql.append(" WHERE b.identity_card=? and a.detail_type=? and a.flag=1 and a.is_main = ? ");
        try (ServerSqlSession session = getSqlDataSource().session()) {
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    User user = new User();
                    user.setUserId(rs.getString("userid"));
                    return user;
                }
                return null;
            }, idCard, detailType, isMain);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public JobNumberAudit getJobNumAuditByIdCard(String idCard, int detailType) {
        StringBuilder sql = new StringBuilder(256);
        sql.append("SELECT order_id");
        sql.append(" FROM tb_job_number_audit ");
        sql.append(" WHERE identityCard=? and detail_type=? and order_status IN (3,4) ");
        try (ServerSqlSession session = getSqlDataSource().session()) {
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    JobNumberAudit audit = new JobNumberAudit();
                    audit.setOrderId(rs.getString("order_id"));
                    return audit;
                }
                return null;
            }, idCard,detailType);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public boolean addJobNumberAudit(User user, JobNumberAudit audit) {
        List<String> sql_list = new ArrayList<>();
        StringBuilder sb = new StringBuilder();
        sb.append("insert into tb_job_number_audit set ");
        sb.append("order_id='").append(audit.getOrderId()).append("'");
        sb.append(",user_id='").append(audit.getUserId()).append("'");
        sb.append(",create_time=").append(audit.getCreateTime());
        sb.append(",source_type=").append(audit.getSourceType());
        sb.append(",company_name='").append(audit.getSonUserName()).append("'");
        sb.append(",dealer_id='").append(user.getDealerId()).append("'");
        sb.append(",merchant_type=").append(audit.getMerchantType());
        sb.append(",main_user_name='").append(user.getUserName()).append("'");
        sb.append(",main_user_id='").append(user.getUserId()).append("'");
        sb.append(",main_user_phone='").append(user.getPhone()).append("'");
        sb.append(",son_user_name='").append(audit.getSonUserName()).append("'");
        sb.append(",son_user_phone='").append(audit.getSonUserPhone()).append("'");
        sb.append(",identityCard='").append(audit.getIdentityCard()).append("'");
        sb.append(",identityCardAddress='").append(audit.getIdentityCardAddress()).append("'");
        sb.append(",period='").append(audit.getPeriod()).append("'");
        sb.append(",identify_photo_front='").append(audit.getIdentifyPhotoFront()).append("'");
        sb.append(",identify_photo_reverse='").append(audit.getIdentifyPhotoReverse()).append("'");
        sb.append(",identify_photo_hand='").append(audit.getIdentifyPhotoHand()).append("'");
        sb.append(",identify_photo_head='").append(audit.getIdentifyPhotoHead()).append("'");
        sb.append(",sign_picture='").append(audit.getSignPicture()).append("'");
        sb.append(",order_status=").append(audit.getOrderStatus());
        sb.append(",detail_type=").append(user.getDetailType());
        sb.append(",relation_type=").append(audit.getRelationType());
        sb.append(",city_name='").append(audit.getCityName()).append("'");
        sb.append(",city_code='").append(audit.getCityCode()).append("'");
        sb.append(",user_type_id=").append(user.getUserTypeId());
        sb.append(",merchant_id='").append(user.getMerchantId()).append("'");
        sb.append(",imgSource=").append(audit.getImgSource());
        sql_list.add(sb.toString());

        sb.delete(0, sb.length());
        sb.append("insert into tb_job_number_audit_log set ");
        sb.append("order_id='").append(audit.getOrderId()).append("'");
        sb.append(",create_time=").append(audit.getCreateTime());
        sb.append(",status=").append(1);
        sql_list.add(sb.toString());

        int result[] = executeBatch(sql_list);
        return result != null && result.length > 0;
    }

    @Override
    public UserDetailModel getUserDetailList(String userId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select c.is_main,a.create_time,c.user_name,c.phone,c.cityname,a.relation_type,c.createtime,a.audit_time,d.identity_card ");
            sb.append(",d.identity_card_address,d.period,a.sign_picture,t.relation_name,c.is_see_all ");
            sb.append(" from tb_user_client c left join tb_job_number_audit a on a.user_id=c.userid ");
            sb.append(" left join tb_user_client_detail d on d.userid = c.userid ");
            sb.append(" left join tb_relation_type t on t.relation_key = a.relation_type ");
            sb.append(" where c.userid = ? ");
            sb.append(" order by c.createtime desc limit 1 ");
            return session.executeQuery(sb.toString(), rs -> {
                UserDetailModel model = new UserDetailModel();
                if (rs != null && rs.next()) {
                    model.setPhone(rs.getString("c.phone"));
                    model.setUserName(rs.getString("c.user_name"));
                    if (StringUtils.isBlank(rs.getString("a.create_time"))) {
                        model.setCreateTime(rs.getString("c.createtime"));
                    } else {
                        model.setCreateTime(rs.getString("a.create_time"));
                    }
                    model.setCityName(rs.getString("c.cityname"));
                    model.setRelationDesc(rs.getString("t.relation_name"));
                    model.setAuditTime(rs.getString("a.audit_time"));
                    String idNo = rs.getString("d.identity_card");
                    String address = rs.getString("d.identity_card_address");
                    int is_see_all = rs.getInt("is_see_all");
                    if(is_see_all == 1){
                        //不做脱敏
                        model.setIdentityCard(idNo);
                        model.setIdentityCardAddress(address);
                    }else{
                        //脱敏显示
                        String overlay = StringUtils.overlay(idNo, "*********", 5, 14);
                        model.setIdentityCard(overlay);
                        String newAddress = StringUtils.left(address, 3)
                                .concat(StringUtils.removeStart(StringUtils.leftPad(StringUtils.right(address, address.length() - 11),
                                        StringUtils.length(address), "*"), "***"));
                        model.setIdentityCardAddress(newAddress);
                    }
                    model.setPeriod(rs.getString("d.period"));
                    model.setSignPicture(rs.getString("a.sign_picture"));
                }
                return model;
            }, userId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }


    @Override
    public SonJobNumProtocolModel getSonJobNumProtocol(String userId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sql = new StringBuilder(256);
            sql.append("SELECT c.user_name,c.is_main,c.userid,a.sign_picture,a.main_user_name");
            sql.append(" FROM tb_user_client c");
            sql.append(" LEFT JOIN tb_job_number_audit a on c.userid = a.user_id and a.order_status = 1 ");
            sql.append(" WHERE c.userid=?");
            sql.append(" limit 1");
            return session.executeQuery(sql.toString(), rs -> {
                if (rs != null && rs.next()) {
                    SonJobNumProtocolModel model = new SonJobNumProtocolModel();
                    model.setUserId(rs.getString("c.userid"));
                    model.setIsMain(rs.getString("c.is_main"));
                    model.setSignPicture(AliOSSUtil.generatePresignedUrl(AliOSSUtil.getDownloadUrl(rs.getString("a.sign_picture"))));
                    return model;
                }
                log.warn("DB not found user,userId=" + userId);
                return null;
            }, userId);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        }
    }

    @Override
    public Merchant getMerchantById(String merchantId) {
        try (ServerSqlSession session = getSqlDataSource().session()) {
            StringBuilder sb = new StringBuilder(256);
            sb.append("select dealer_id,company_name,user_type_id");
            sb.append(",business_licence,address,popchannel,head_popchannel");
            sb.append(",createtime,devMac,merchant_type,isSignAgreement");
            sb.append(",sign_time,sign_picture,bond,status");
            sb.append(",merchant_attribute,attribute_status,flag,banner_ids");
            sb.append(",super_dealer_id,super_dealer_name,super_invite_code");
            sb.append(",ytsk_status,detail_type");
            sb.append(" from tb_merchant");
            sb.append(" where merchant_id=?");
            sb.append(" limit 1");

            return session.executeQuery(sb.toString(), rs -> {
                if (rs != null && rs.next()) {
                    Merchant merchant = new Merchant();
                    merchant.setCompanyName(rs.getString("company_name"));
                    merchant.setUserTypeId(rs.getInt("user_type_id"));
                    merchant.setBusinessLicence(rs.getString("business_licence"));
                    merchant.setAddress(rs.getString("address"));
                    merchant.setPopChannel(rs.getString("popchannel"));
                    merchant.setHeadPopChannel(rs.getString("head_popchannel"));
                    merchant.setCreateTime(rs.getLong("createtime"));
                    merchant.setDevMac(rs.getString("devMac"));
                    merchant.setMerchantType(rs.getInt("merchant_type"));
                    merchant.setIsSignAgreement(rs.getInt("isSignAgreement"));
                    merchant.setSignTime(rs.getLong("sign_time"));
                    merchant.setSignPicture(rs.getString("sign_picture"));
                    merchant.setBond(rs.getInt("bond"));
                    merchant.setStatus(rs.getInt("status"));
                    merchant.setMerchant_attribute(rs.getInt("merchant_attribute"));
                    merchant.setAttribute_status(rs.getInt("attribute_status"));
                    merchant.setFlag(rs.getInt("flag"));
                    merchant.setBannerIds(rs.getString("banner_ids"));
                    merchant.setSuperDealerId(rs.getString("super_dealer_id"));
                    merchant.setSuperDealerName(rs.getString("super_dealer_name"));
                    merchant.setYtskStatus(rs.getInt("ytsk_status"));
                    merchant.setDetailType(rs.getInt("detail_type"));
                    merchant.setSuperInviteCode(rs.getString("super_invite_code"));
                    return merchant;
                }
                return null;
            }, merchantId);
        } catch (Exception ex) {
            log.error(ex.getMessage(), ex);
            return null;
        }
    }
}
